from sqlalchemy import (ARRAY, Column, Date, ForeignKeyConstraint, Integer,
                        PrimaryKeyConstraint, Sequence, String, Table, Text, text)
from sqlalchemy.orm import declarative_base, mapped_column, relationship
from sqlalchemy.orm.base import Mapped

Base = declarative_base()
metadata = Base.metadata

post_user = Table(
    'post_user', metadata,
    Column('postid', Integer, nullable=False),
    Column('userid', Integer, nullable=False),
    ForeignKeyConstraint(['postid'], ['cs-curriculum-design.post.post_id'], name='post-user_post_postid_fk'),
    ForeignKeyConstraint(['userid'], ['cs-curriculum-design.user.userid'], name='post-user_user_userid_fk'),
    PrimaryKeyConstraint('postid', 'userid', name='post-user_pk'),
    schema='cs-curriculum-design'
)


class User(Base):
    __tablename__ = 'user'
    __table_args__ = (
        PrimaryKeyConstraint('userid', name='userid'),
        {'schema': 'cs-curriculum-design'}
    )

    userid = mapped_column(Integer)
    username = mapped_column(String(20), nullable=False, server_default=text('gen_random_uuid()'))
    password = mapped_column(Text, nullable=False)
    role = mapped_column(Integer, nullable=False, server_default=text('1'),
                         comment='1对应投稿人，2对应审核人员，3对应管理员，0对应匿名用户')
    email = mapped_column(Text)

    post: Mapped['Post'] = relationship('Post', secondary=post_user, back_populates='user')


class Banner(Base):
    __tablename__ = 'banner'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='banner_pk'),
        {'schema': 'cs-curriculum-design'}
    )

    id = mapped_column(Integer)
    content = mapped_column(Text, nullable=False)
    expire_date = mapped_column('expire-date', Date)


class Post(Base):
    __tablename__ = 'post'
    __table_args__ = (
        PrimaryKeyConstraint('post_id', name='post-content_pk'),
        {'schema': 'cs-curriculum-design'}
    )

    post_id = mapped_column(Integer, Sequence('post-content_postid_seq', schema='cs-curriculum-design'))
    post_content = mapped_column(Text)
    post_file = mapped_column(ARRAY(Text()))
    reviewed = mapped_column(Integer, server_default=text('0'), comment='0 未审阅 1 审阅通过 2 审阅不合格')
    post_title = mapped_column(Text, server_default=text("'Generated Title'::text"))

    user: Mapped['User'] = relationship('User', secondary=post_user, back_populates='post')
